//Comment below out if not using EmulateDeriveParameters
//#define EmulateDeriveParameters 
//Comment above out if not using EmulateDeriveParameters
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace DingFan
{
    public class DataAccess : IDisposable
    {

        private string strConn;
        private SqlCommand cmd;

        SqlConnection _dbConn;
        SProcList _sprocs;  //sproc parameter info cache
        SqlParameterCollection _lastParams; //used by Param()
        SqlTransaction _trans; // added by ATW to handle SQL Transactions

        //Configuration cls = new Configuration();
        public DataAccess(string dbServer, string dbName, string userName, string password)
        {
            strConn = "server=" + dbServer + ";database=" + dbName + ";uid=" + userName + ";pwd=" + password;
            this.StrConn = strConn;
            cmd = new SqlCommand();
            _dbConn = new SqlConnection(strConn);
            _sprocs = new SProcList(this);

        }
        public DataAccess(string strDBConn)
        {
            this.StrConn = strConn;
            cmd = new SqlCommand();
            _dbConn = new SqlConnection(strDBConn);
            _sprocs = new SProcList(this);
        }
        public string StrConn { get; set; }
        public SqlConnection SqlConn
        {
            get
            {
                return _dbConn;

            }
        }
        public void Dispose()
        {
            System.GC.SuppressFinalize(this);
        }

        public void Open()
        {
            try
            {
                if (_dbConn.State != ConnectionState.Open)
                {
                    _dbConn.Open();
                }
            }
            catch (Exception ex)
            {
                throw (new Exception(_dbConn.ConnectionString));
            }
        }

        public void Close()
        {
            if (_dbConn.State == ConnectionState.Open)
            {
                _dbConn.Close();
            }
        }

        public void TransBegin()
        {
            Open();

            _trans = _dbConn.BeginTransaction();
        }

        public void TransCommit()
        {
            _trans.Commit();
            Close();
        }

        public void TransRollback()
        {
            _trans.Rollback();
            Close();
        }

        SqlCommand NewSProc(string procName)
        {
            SqlCommand cmd;
            if (_trans != null)
            {
                cmd = new SqlCommand(procName, _dbConn, _trans);
            }
            else
            {
                cmd = new SqlCommand(procName, _dbConn);
            }
            cmd.CommandType = CommandType.StoredProcedure;

#if EmulateDeriveParameters   //see below for our 
                              //own DeriveParameters
            MySqlCmdBuilder.DeriveParameters(cmd);

#else
            Open();
            SqlCommandBuilder.DeriveParameters(cmd);
            //SQL treats OUT params as REF params 
            //(thus requiring those parameters to be passed in)
            //if that's what you really want, remove 
            //the next three lines
            foreach (SqlParameter prm in cmd.Parameters)
                if (prm.Direction == ParameterDirection.InputOutput)
                    //make param a true OUT param
                    prm.Direction = ParameterDirection.Output;
#endif

            return cmd;
        }

        SqlCommand FillParams(string procName, params object[] vals)
        {
            //get cached info (or cache if first call)
            SqlCommand cmd = _sprocs[procName];
            // set time out time
            cmd.CommandTimeout = 600;
            //fill parameter values for stored procedure call
            int i = 0;
            foreach (SqlParameter prm in cmd.Parameters)
            {
                //we got info for ALL the params - only 
                //fill the INPUT params
                if (prm.Direction == ParameterDirection.Input || prm.Direction == ParameterDirection.InputOutput)
                {
                    prm.Value = vals[i++];
                }
            }
            //make sure the right number of parameters was passed
            // Debug.Assert(i == (vals == null ? 0 : vals.Length));

            //for subsequent calls to Param()
            _lastParams = cmd.Parameters;
            return cmd;
        }


        SqlCommand TransFillParams(string procName, params object[] vals)
        {
            //get cached info (or cache if first call)
            SqlCommand cmd = _sprocs[procName];
            cmd.Transaction = _trans;

            //fill parameter values for stored procedure call
            int i = 0;
            foreach (SqlParameter prm in cmd.Parameters)
            {
                //we got info for ALL the params - only 
                //fill the INPUT params
                if (prm.Direction == ParameterDirection.Input || prm.Direction == ParameterDirection.InputOutput)
                {
                    prm.Value = vals[i++];
                }
            }
            //make sure the right number of parameters was passed
            // Debug.Assert(i == (vals == null ? 0 : vals.Length));

            //for subsequent calls to Param()
            _lastParams = cmd.Parameters;
            return cmd;
        }

        //handy routine if you are in control of the input.
        //but if user input, vulnerable to sql injection attack
        //public DataRowCollection QueryRows(string strQry)
        //{
        //    DataTable dt = new DataTable();
        //    new SqlDataAdapter(strQry, _dbConn).Fill(dt);
        //    return dt.Rows;
        //}
        public DataTable QueryTable(string strQry)
        {
            DataTable dt = new DataTable();
            try
            {
                new SqlDataAdapter(strQry, _dbConn).Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return dt;
            }
            finally
            {
                Close();
            }
        }
        public int TransExecQuery(string strQry)
        {
            SqlCommand cmd = new SqlCommand(strQry, _dbConn);
            cmd.Transaction = _trans;
            _lastParams = cmd.Parameters;
            return cmd.ExecuteNonQuery();
        }

        public int ExecQuery(string strQry)
        {
            int count = 0;
            try
            {
                using (SqlCommand cmd = new SqlCommand(strQry, _dbConn))
                {
                    Open();
                    _lastParams = cmd.Parameters;
                    count = cmd.ExecuteNonQuery();
                    return count;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Close();
            }
        }

        public object ExecSProcScalar(string strQry)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(strQry, _dbConn))
                {
                    Open();
                    _lastParams = cmd.Parameters;
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Close();
            }
        }

        public int ExecSProcScalar(string procName, params object[] vals)
        {
            int retVal = -1;  //some error code

            try
            {
                Open();
                //FillParams(procName, vals).ExecuteScalar();
                retVal = Convert.ToInt32(FillParams(procName, vals).ExecuteScalar());

            }
            //any special handling for SQL-generated error here
            catch (System.Data.SqlClient.SqlException esql)
            {
                //MessageBox.Show(esql.Message, "esql ExecSProcScalar Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                Close();
            }
            return retVal;
        }

        /* 
         * 
         * ExecSProc - takes in a stored procedure and input values, then executes a nonquery sql command
         * 
         **/
        public int ExecSProc(string procName, params object[] vals)
        {
            int retVal = -1;  //some error code

            try
            {
                Open();
                FillParams(procName, vals).ExecuteNonQuery();
                retVal = (int)_lastParams[0].Value;

            }
            //any special handling for SQL-generated error here
            catch (System.Data.SqlClient.SqlException esql)
            {
                //MessageBox.Show(esql.Message, "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                Close();
            }
            return retVal;
        }

        public int ExecSProcCount(string procName, params object[] vals)
        {
            int count = 0;  //some error code

            try
            {
                Open();
             count=   FillParams(procName, vals).ExecuteNonQuery();
            }
            //any special handling for SQL-generated error here
            catch (Exception ex)
            {
                //MessageBox.Show(esql.Message, "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                Close();
            }
            return count;
        }

        public int TransExecSProc(string procName, params object[] vals)
        {
            return TransFillParams(procName, vals).ExecuteNonQuery();
        }

        public object TransExecuteScalar(string procName, params object[] vals)
        {
            return TransFillParams(procName, vals).ExecuteScalar();
        }

        /* 
         * 
         * ExecSProcDS - takes in a stored procedure and input values, then returns a DataSet
         * 
         **/
        public DataSet ExecSProcDS(string procName, params object[] vals)
        {
            DataSet ds = new DataSet();

            try
            {
                Open();
                new SqlDataAdapter(FillParams(procName, vals)).Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException esql)
            {
                //MessageBox.Show(esql.Message, "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                Close();
            }
            return ds;
        }

        /* 
         * 
         * ExecSProcDT - takes in a stored procedure, table name and input values, then returns a DataTable
         * 
         **/
        public DataTable ExecSProcDT(string procName, string dataTableName, params object[] vals)
        {
            DataTable dt = new DataTable();

            try
            {
                Open();
                new SqlDataAdapter(FillParams(procName, vals)).Fill(dt);
                dt.TableName = dataTableName;
            }
            catch (System.Data.SqlClient.SqlException esql)
            {
                //MessageBox.Show(esql.Message, "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                Close();
            }
            return dt;
        }

        //get parameter from most recent ExecSProc
        public object Param(string param)
        {
            return _lastParams[param].Value;
        }

        class SProcList : DictionaryBase
        {
            DataAccess _db;
            public SProcList(DataAccess db)
            {
                _db = db;
            }

            public SqlCommand this[string name]
            {
                get
                {      //read-only, "install on demand"
                    if (!Dictionary.Contains(name))
                    {
                        Dictionary.Add(name, _db.NewSProc(name));
                    }
                    return (SqlCommand)Dictionary[name];
                }
            }
        }

#if EmulateDeriveParameters
        class MySqlCmdBuilder {
            static SqlTypeMap _sqlTypeMap = null;
            class SqlTypeMap : DictionaryBase {
                public SqlDbType this[string key]
                  { get { return (SqlDbType)Dictionary[key]; }}
                public void Add(string key, SqlDbType value)  
                  { Dictionary.Add(key, value); }
            }

            //static helper class - don't allow instantiation
            private MySqlCmdBuilder() {}    

            public static void DeriveParameters(SqlCommand cmd)
            {
                EnsureTypeMap();

                //cmd.Parameters[0] will always hold 
                //the sproc return value
                SqlParameter prmRet = 
                    new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                prmRet.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(prmRet);

                string qrySProc = 
                    "SELECT parameter_name as name"
                        + ", data_type as xtype"
                        + ", cast(isnull(character_maximum_length, " + 
                                           "numeric_scale) as int) as prec"
                        + ", case when parameter_mode like '%out%' " + 
                                           "then 1 else 0 end as isoutparam"
                    + " FROM INFORMATION_SCHEMA.PARAMETERS"
                    + " WHERE specific_name = '" + cmd.CommandText + "'"
                    + " ORDER BY ordinal_position";

                //query SQL-server for given sproc's parameter info
                DataTable dt = new DataTable(); 
                new SqlDataAdapter(qrySProc, cmd.Connection).Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    SqlParameter prm = new SqlParameter(
                            (string)dr[0],               //dr["name"] 
                            _sqlTypeMap[(string)dr[1]],  //dr["xtype"]
                            (int)dr[2]);                 //dr["prec"]
                    if ((int)dr[3] == 1)                 //isoutparam?
                        prm.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(prm);
                }
            }

            static void EnsureTypeMap()
            {
                if (_sqlTypeMap == null) {
                    _sqlTypeMap = new SqlTypeMap();
                    _sqlTypeMap.Add("bit",          SqlDbType.Bit);
                    _sqlTypeMap.Add("int",          SqlDbType.Int);
                    _sqlTypeMap.Add("smallint",     SqlDbType.SmallInt);
                    _sqlTypeMap.Add("tinyint",      SqlDbType.TinyInt);
                    _sqlTypeMap.Add("datetime",     SqlDbType.DateTime);
                    _sqlTypeMap.Add("smalldatetime",SqlDbType.SmallDateTime);
                    _sqlTypeMap.Add("char",         SqlDbType.Char);
                    _sqlTypeMap.Add("varchar",      SqlDbType.VarChar);
                    _sqlTypeMap.Add("nchar",        SqlDbType.NChar);
                    _sqlTypeMap.Add("nvarchar",     SqlDbType.NVarChar);
                    //add more here if SqlTypeMap[...] throws an exception
                }
            }
        }
#endif

    }

}